package kr.or.kead.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import kr.or.kead.domain.RequestCourse;
import kr.or.kead.domain.ViewRequestCourse;
import kr.or.kead.utils.MySqlCon;

public class DaoRequestCourse implements DaoTable {

	@Override
	public int insertDao(Object obj) {
		Connection con = MySqlCon.getConn();
		RequestCourse rc= (RequestCourse) obj;
		String sql = "insert into requestcourse values(?,?,?,?)";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, rc.getIdx());
			pstmt.setInt(2, rc.getStd_idx());
			pstmt.setInt(3, rc.getCourse_code());
			pstmt.setInt(4, rc.getGrade());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try { 
				pstmt.close();
				con.close();
			}catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int updateDao(Object obj) {
		Connection con = MySqlCon.getConn();
		RequestCourse rc= (RequestCourse) obj;
		String sql = "update requestcourse set idx=?, std_idx =?, course_code=?, grade=? where idx=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, rc.getIdx());
			pstmt.setInt(2, rc.getStd_idx());
			pstmt.setInt(3, rc.getCourse_code());
			pstmt.setInt(4, rc.getGrade());
			pstmt.setInt(5, rc.getIdx());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public int deleteDao(int regNo) {
		Connection con = MySqlCon.getConn();
		String sql = "delete from requestcourse where idx=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, regNo);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return 0;
	}

	@Override
	public ArrayList<Object> selectDao() {
		Connection con = MySqlCon.getConn();
		String sql = "select * from view_request_infostd_course";
		ArrayList<Object> reqestCourses = new ArrayList<>();
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()){
				reqestCourses.add(new ViewRequestCourse(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4),
						rs.getInt(5),rs.getString(6),rs.getString(7),rs.getString(8),rs.getString(9),rs.getString(10),rs.getString(11)));
			}
			return reqestCourses;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	@Override
	public Object selectTableById(int stdId) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public ArrayList<String> selectTableAllList() {
		Connection con = MySqlCon.getConn();
		String sql = "select idx, course_code from requestcourse";
		ArrayList<String> requests = new ArrayList<>();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			pstmt = con.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) requests.add(rs.getInt(1)+" : "+rs.getInt(2));
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				rs.close();
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return requests;
	}

	@Override
	public Object selectTableByEmail(String email) {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int updataTableByEmail(String pass, String email) {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public ArrayList<String> selectTableAllNames() {
		// TODO Auto-generated method stub
		return null;
	}
	
	public int selectMaxCode(){
		Connection con = MySqlCon.getConn();
		String sql = "select max(idx) from requestcourse";
		PreparedStatement pstmt = null;
		try {
			pstmt = con.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()){
				return rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				pstmt.close();
				con.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}
}
